Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Access Methods

The following hints provide the optimizer with the specific access method you think is more appropriate, based on information you know about your data and your application. These hints are valid only if the referenced index or cluster is available and the syntax of the SQL statement is correct. If the syntax is incorrect or the referenced index or cluster is not available, the hint is ignored.

When specifying an access path for an SQL statement, the table to be accessed must be specified exactly. If the table is accessed with an alias, you must specify the alias.

AND_EQUAL

The AND_EQUAL hint specifies an execution plan that uses an access path that merges the scans of several single-column indexes.

The syntax of this hint is as follows:

/*+ AND_EQUAL( table index index [ index .... index ] ) */

CLUSTER

The CLUSTER hint specifies that the optimizer should choose a cluster scan to access the specified table.

The syntax of this hint is as follows:

/*+ CLUSTER( table ) */

FULL

The FULL hint specifies that the optimizer should choose a full-table scan to access the specified table.

The syntax of this hint is as follows:

/*+ FULL( table ) */

This hint specifies the use of a full-table scan, even though there may be an index present on this table and the index key is specified in the WHERE clause.


TIP:  If you have an indexed table and you know that a certain value in your SELECT statement has a large number of duplicates (which is inefficient for indexes), you may want to use the FULL hint to bypass the index.

HASH

The HASH hint specifies that the optimizer should choose a hash scan to access the specified table.

The syntax of this hint is as follows:

/*+ HASH( table ) */

INDEX

The INDEX hint specifies that the optimizer should choose an index scan to access the specified table.

The syntax of this hint is as follows:

/*+ INDEX( table index [ index .. index ] ) */

This hint may contain one or more indexes. The following choices may be made by the optimizer:

  If no index is specified in the hint, the optimizer determines the cost of each index that could be used to access the specified table. Once this determination is made, the index with the lowest cost is chosen for the index scan. The optimizer may choose to scan multiple indexes and merge the result if this path has the lowest cost. A full-table scan is not considered.
  If one index is specified in the hint, this index is used for the index scan. The optimizer does not consider a full-table scan or any other index scans.
  If multiple indexes are specified in the hint, the optimizer determines the cost of each index in the list that could be used to access the specified table. Once this determination is made, the index with the lowest cost is chosen for the index scan. The optimizer may choose to scan multiple indexes in the list and merge the result if this path has the lowest cost. A full-table scan is not considered, nor are any indexes not in the list.

The INDEX hint specifies the use of an index scan, even though the optimizer may not choose an index scan because of a low number of distinct values. If you know that the value in the WHERE clause is somewhat unique, you can improve performance by using this hint.


TIP:  If you have an indexed table, and you know that a certain value in your SELECT statement has a small number of distinct values (which is inefficient for indexes), but the value you are selecting is somewhat distinct, you may benefit from this hint.

INDEX_ASC

The INDEX_ASC hint specifies that the optimizer should choose an index scan to access the specified table.

The syntax of this hint is as follows:

/*+ INDEX_ASC( table index [ index .. index ] ) */

This hint can contain one or more indexes. The INDEX_ASC hint is similar to the INDEX hint except that if the statement specifies an index range scan, the entries are scanned in ascending order of their indexed values.


NOTE:  This method is currently the default behavior for a range scan. However, because Oracle does not guarantee that this method will always be the default behavior, this hint is provided.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.